package bbalgae_JavaJDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class Ex01_SungJukDAO implements Ex01_ISungJukDAO {

	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:bbalgae/bbalgae@112.216.253.125:1521:xe";
	private String insertSQL = "insert into Ex01SungJuk values (exam.nextval,?,?,?,?,?,?,?)";
	private String selectSQL = "select * from Ex01SungJuk" ;
	private String updateSQL = "update Ex01SungJuk set name=?,kor=?,eng=?,mat=?,tot=?,avg=?,grd=? where sjno=?";
	private String deleteSQL="delete Ex01SungJuk where sjno=?";
	@Override
	public boolean insertSungJuk(Ex01_SungJukVO s) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;

		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
			// pstmt.setLong(1,s.getSjno() );
			pstmt.setString(1, s.getName());
			pstmt.setInt(2, s.getKor());
			pstmt.setInt(3, s.getEng());
			pstmt.setInt(4, s.getMat());
			pstmt.setInt(5, s.getTot());
			pstmt.setFloat(6, s.getAvg());
			pstmt.setString(7, s.getGrd());

			pstmt.executeUpdate();
			result = true;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.closeSungJuk(conn, pstmt, null);
		}
		return result;

	}

	private void closeSungJuk(Connection c, PreparedStatement p, ResultSet r) {
		if (r != null)
			try {
				r.close();
			} catch (Exception e) {
			}
		if (p != null)
			try {
				p.close();
			} catch (Exception e) {
			}
		if (c != null)
			try {
				c.close();
			} catch (Exception e) {
			}

	}

	private Connection openDatabase() {
		try {
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Ex01_SungJukVO> selectSungJuk() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Ex01_SungJukVO> result = new ArrayList<>();

		try {
			conn=openDatabase();
			pstmt=conn.prepareStatement(selectSQL);
			rs=pstmt.executeQuery();
			while(rs.next()){
				Ex01_SungJukVO sj = new Ex01_SungJukVO();
				sj.setSjno(rs.getInt("sjno"));
				sj.setName(rs.getString("name"));
				sj.setKor(rs.getInt("kor"));
				sj.setEng(rs.getInt("eng"));
				sj.setMat(rs.getInt("mat"));
				sj.setTot(rs.getInt("tot"));
				sj.setAvg(rs.getFloat("avg"));
				sj.setGrd(rs.getString("grd"));
				result.add(sj);
			}
			
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeSungJuk(conn, pstmt, rs);
		}

		return result;
	}

	@Override
	public boolean updateSungJuk(Ex01_SungJukVO s) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{ 
			conn=this.openDatabase();
			pstmt=conn.prepareStatement(updateSQL);
			pstmt.setString(1, s.getName());
			pstmt.setInt(2, s.getKor());
			pstmt.setInt(3, s.getEng());
			pstmt.setInt(4,s.getMat());
			pstmt.setInt(5,s.getTot());
			pstmt.setFloat(6, s.getAvg());
			pstmt.setString(7, s.getGrd());
			pstmt.setInt(8,s.getSjno());
			pstmt.executeUpdate();
			result =true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeSungJuk(conn,pstmt, null);
		}
		
		
		return result;
	}

	@Override
	public boolean deleteSungJuk(Ex01_SungJukVO s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn=this.openDatabase();
			pstmt=conn.prepareStatement(deleteSQL);
			pstmt.setInt(1, s.getSjno());
			pstmt.executeUpdate();
			result = true;
			
		}catch(Exception e){
			e.printStackTrace();
			
		}finally{
			this.closeSungJuk(conn, pstmt, null);
		}
		
		
		
		return result;
	}

}
